﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsNCtoWms
{
    public partial class Form1 : Form
    {
        string OraconnectionString = ConfigurationManager.AppSettings["OraconnectionString"].ToString();
        string SqlconnectionString = ConfigurationManager.AppSettings["SqlconnectionString"].ToString();
        string SqlconnectionStringYY = ConfigurationManager.AppSettings["SqlconnectionStringYY"].ToString();
        string SqlconnectionStringZJG = ConfigurationManager.AppSettings["SqlconnectionStringZJG"].ToString();
        DateTime cqtime;
        DateTime yytime;
        DateTime zjgtime;
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //string sql;
            //sql = "truncate table Dic_Inventory ";
            //SqlExecuteSql(SqlconnectionString, sql);
            //sql = @" select INVCODE, INVNAME, INVSHORTNAME, INVSPEC, INVTYPE, INVCLASSCODE, INVCLASSNAME ,ts  from cq.v_produce  ";
            //DataTable dt = OraQuery(sql);
            //sql = @"SELECT      INVCODE FROM      Dic_Inventory ";
            //DataTable tb1 = SqlGetDataTable(SqlconnectionString, sql);

            //if (dt.Rows.Count > 0)
            //{
            //    for (int i = 0; i < dt.Rows.Count; i++)
            //    {
            //        if (!IsColumnIncludeData(tb1, "INVCODE", dt.Rows[i]["INVCODE"].ToString()))
            //        {
            //            string sql2;
            //            sql2 = "insert into Dic_Inventory(guid, INVCODE, INVNAME, INVSHORTNAME, INVSPEC, INVTYPE, INVCLASSCODE, INVCLASSNAME)";
            //            sql2 += " values( newid(),'" + dt.Rows[i]["INVCODE"].ToString() + "','" + dt.Rows[i]["INVNAME"].ToString() + "',";
            //            sql2 += "'" + dt.Rows[i]["INVSHORTNAME"].ToString() + "','" + dt.Rows[i]["INVSPEC"].ToString() + "','" + dt.Rows[i]["INVTYPE"].ToString() + "'";
            //            sql2 += ",'" + dt.Rows[i]["INVCLASSCODE"].ToString() + "','" + dt.Rows[i]["INVCLASSNAME"].ToString() + "')";
            //            SqlExecuteSql(SqlconnectionString, sql2);

            //        }


            //    }
            //}
            取重庆();
            MessageBox.Show("从NC同步产品到重庆档案完成!");

        }
        //取重庆
        private void 取重庆()
        {
            string sql;
            //sql = "truncate table Dic_Inventory ";
            //SqlExecuteSql(SqlconnectionString, sql);
            删重复(SqlconnectionString);
            

            sql = @" select INVCODE, INVNAME, INVSHORTNAME, INVSPEC, INVTYPE, INVCLASSCODE, INVCLASSNAME ,ts  from cq.v_produce where ts>='"+ cqtime.ToString("yyyy-MM-dd HH:mm:ss") + "' order by ts asc";
            DataTable dt = OraQuery(sql);
            sql = @"SELECT      INVCODE FROM      Dic_Inventory ";
            DataTable tb1 = SqlGetDataTable(SqlconnectionString, sql);

            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (!IsColumnIncludeData(tb1, "INVCODE", dt.Rows[i]["INVCODE"].ToString()))
                    {
                        string sql2;
                        sql2 = "insert into Dic_Inventory(guid, INVCODE, INVNAME, INVSHORTNAME, INVSPEC, INVTYPE, INVCLASSCODE, INVCLASSNAME,[CreateDate])";
                        sql2 += " values( newid(),'" + dt.Rows[i]["INVCODE"].ToString() + "','" + dt.Rows[i]["INVNAME"].ToString() + "',";
                        sql2 += "'" + dt.Rows[i]["INVSHORTNAME"].ToString() + "','" + dt.Rows[i]["INVSPEC"].ToString() + "','" + dt.Rows[i]["INVTYPE"].ToString() + "'";
                        sql2 += ",'" + dt.Rows[i]["INVCLASSCODE"].ToString() + "','" + dt.Rows[i]["INVCLASSNAME"].ToString() + "','"+dt.Rows[i]["ts"].ToString()+"')";
                        SqlExecuteSql(SqlconnectionString, sql2);

                    }
                    else
                    {
                        string sql2;
                        sql2 = $"delete from Dic_Inventory where INVCODE = '{dt.Rows[i]["INVCODE"].ToString()}'";
                        SqlExecuteSql(SqlconnectionString, sql2);

                        sql2 = "insert into Dic_Inventory(guid, INVCODE, INVNAME, INVSHORTNAME, INVSPEC, INVTYPE, INVCLASSCODE, INVCLASSNAME,[CreateDate])";
                        sql2 += " values( newid(),'" + dt.Rows[i]["INVCODE"].ToString() + "','" + dt.Rows[i]["INVNAME"].ToString() + "',";
                        sql2 += "'" + dt.Rows[i]["INVSHORTNAME"].ToString() + "','" + dt.Rows[i]["INVSPEC"].ToString() + "','" + dt.Rows[i]["INVTYPE"].ToString() + "'";
                        sql2 += ",'" + dt.Rows[i]["INVCLASSCODE"].ToString() + "','" + dt.Rows[i]["INVCLASSNAME"].ToString() + "','" + dt.Rows[i]["ts"].ToString() + "')";
                        SqlExecuteSql(SqlconnectionString, sql2);
                    }

                }
                cqtime =DateTime.Parse( dt.Rows[dt.Rows.Count - 1]["ts"].ToString());
                sql = "update Bus_最新时间 set  [最新时间]='" + cqtime + "'  ";
                SqlExecuteSql(SqlconnectionString, sql);//重庆
                sql = @"insert into T_VIEW_CIM (T_GOOD_CODE,T_GOOD_NAME,T_GOOD_MODEL,EXP4,T_GOOD_STATUE)
                        select [INVCODE],[INVNAME],[INVSPEC],[INVTYPE],'启用' from  [Dic_Inventory] where  not exists (select 1 from T_VIEW_CIM where T_GOOD_CODE = [INVCODE])
                        and INVCLASSNAME in ( '热轧','锭类')";
                SqlExecuteSql(SqlconnectionString, sql);//重庆
            }
        }

        public static Boolean IsColumnIncludeData(DataTable dt, String columnName, string fieldData)
        {
            if (dt == null)
            {
                return false;
            }
            else
            {
                DataRow[] dataRows = dt.Select(columnName + "='" + fieldData + "'");
                if (dataRows.Length.Equals(1))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }

        }
        DataTable OraQuery(string sql)
        {
            using (OracleConnection connection = new OracleConnection(OraconnectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    OracleDataAdapter command = new OracleDataAdapter(sql, connection);
                    command.Fill(ds, "ds");
                    return ds.Tables[0];
                }
                catch (System.Data.OracleClient.OracleException ex)
                {

                }
                return new DataTable();

            }
        }
        int OraExecuteSql(string sql)
        {
            using (OracleConnection connection = new OracleConnection(OraconnectionString))
            {
                int result;
                result = 0;
                try
                {
                    connection.Open();
                    OracleCommand command = new OracleCommand(sql, connection);
                    result = command.ExecuteNonQuery();
                }
                catch (System.Data.OracleClient.OracleException ex)
                {
                    throw new Exception(ex.Message);
                }
                return result;

            }
        }
        public static DataTable SqlGetDataTable(string ConnectionString, string mysql)
        {
            try
            {

                DataSet ds = new DataSet();
                SqlConnection m_myConnection = new SqlConnection(ConnectionString);
                m_myConnection.Open();
                SqlCommand cmd = new SqlCommand(mysql, m_myConnection);
                cmd.CommandTimeout = 1000000;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
                m_myConnection.Close();
                m_myConnection.Dispose();
                return ds.Tables[0];


            }
            catch
            {
                return null;
            }
        }
        private static int SqlExecuteSql(string ConnectionString, string mysql)
        {

            try
            {

                int result;
                SqlConnection m_myConnection = new SqlConnection(ConnectionString);
                m_myConnection.Open();
                SqlCommand cmd = new SqlCommand(mysql, m_myConnection);
                cmd.CommandTimeout = 1000000;
                cmd.CommandText = mysql;
                result = cmd.ExecuteNonQuery();
                m_myConnection.Close();
                m_myConnection.Dispose();
                return result;
            }
            catch
            {
                return 0;
            }

        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            取重庆();
            取张家港();
            取岳阳();
            //string sql;
            ////sql = "truncate table Dic_Inventory ";
            ////SqlExecuteSql(SqlconnectionString, sql);

            //sql = @" select* from cq.v_produce where ts >='" + cqtime + "' order by ts ";
            //DataTable dt = OraQuery(sql);
            ////sql = @"SELECT      INVCODE FROM      Dic_Inventory ";
            ////DataTable tb1 = SqlGetDataTable(SqlconnectionString, sql);

            //if (dt.Rows.Count > 0)
            //{
            //    for (int i = 0; i < dt.Rows.Count; i++)
            //    {
            //        //if (!IsColumnIncludeData(tb1, "INVCODE", dt.Rows[i]["INVCODE"].ToString()))
            //        //{
            //        string sql2;
            //        sql2 = "delete Dic_Inventory where INVCODE='" + dt.Rows[i]["INVCODE"].ToString() + "'";
            //        SqlExecuteSql(SqlconnectionString, sql2);

            //        sql2 = "insert into Dic_Inventory(guid, INVCODE, INVNAME, INVSHORTNAME, INVSPEC, INVTYPE, INVCLASSCODE, INVCLASSNAME)";
            //        sql2 += " values( newid(),'" + dt.Rows[i]["INVCODE"].ToString() + "','" + dt.Rows[i]["INVNAME"].ToString() + "',";
            //        sql2 += "'" + dt.Rows[i]["INVSHORTNAME"].ToString() + "','" + dt.Rows[i]["INVSPEC"].ToString() + "','" + dt.Rows[i]["INVTYPE"].ToString() + "'";
            //        sql2 += ",'" + dt.Rows[i]["INVCLASSCODE"].ToString() + "','" + dt.Rows[i]["INVCLASSNAME"].ToString() + "')";
            //        SqlExecuteSql(SqlconnectionString, sql2);

            //        //}


            //    }

            //}
            //cqtime = DateTime.Now.AddDays(-0.1);
            //sql = "update [Bus_最新时间] set [最新时间]='" + cqtime + "'";
            //SqlExecuteSql(SqlconnectionString, sql);

            ////
            ////sql = "truncate table Dic_Inventory ";
            ////SqlExecuteSql(SqlconnectionStringYY, sql);
            //sql = @" select * from yy.v_produce where ts >='" + yytime + "' order by ts ";
            //DataTable dtyy = OraQuery(sql);
            ////sql = @"SELECT      INVCODE FROM      Dic_Inventory ";
            ////DataTable tbyy = SqlGetDataTable(SqlconnectionStringYY, sql);

            //if (dtyy.Rows.Count > 0)
            //{
            //    for (int i = 0; i < dtyy.Rows.Count; i++)
            //    {
            //        //if (!IsColumnIncludeData(tbyy, "INVCODE", dtyy.Rows[i]["INVCODE"].ToString()))
            //        //{
            //        string sql2;
            //        sql2 = "delete Dic_Inventory where INVCODE='" + dtyy.Rows[i]["INVCODE"].ToString() + "'";
            //        SqlExecuteSql(SqlconnectionStringYY, sql2);

            //        sql2 = "insert into Dic_Inventory(guid, INVCODE, INVNAME, INVSHORTNAME, INVSPEC, INVTYPE, INVCLASSCODE, INVCLASSNAME)";
            //        sql2 += " values( newid(),'" + dtyy.Rows[i]["INVCODE"].ToString() + "','" + dtyy.Rows[i]["INVNAME"].ToString() + "',";
            //        sql2 += "'" + dtyy.Rows[i]["INVSHORTNAME"].ToString() + "','" + dtyy.Rows[i]["INVSPEC"].ToString() + "','" + dtyy.Rows[i]["INVTYPE"].ToString() + "'";
            //        sql2 += ",'" + dtyy.Rows[i]["INVCLASSCODE"].ToString() + "','" + dtyy.Rows[i]["INVCLASSNAME"].ToString() + "')";
            //        SqlExecuteSql(SqlconnectionStringYY, sql2);

            //        //}

            //    }

            //}
            //yytime = DateTime.Now.AddDays(-0.1);
            //sql = "update [Bus_最新时间] set [最新时间]='" + yytime + "'";
            //SqlExecuteSql(SqlconnectionStringYY, sql);


            //// sql = "truncate table Dic_Inventory ";
            ////SqlExecuteSql(SqlconnectionStringZJG, sql);
            //sql = @" select * from zjg.v_produce  where ts >='" + zjgtime + "' order by ts ";
            //DataTable dtzjg = OraQuery(sql);
            ////sql = @"SELECT      INVCODE FROM      Dic_Inventory ";
            ////DataTable tbzjg = SqlGetDataTable(SqlconnectionStringZJG, sql);

            //if (dtzjg.Rows.Count > 0)
            //{
            //    for (int i = 0; i < dtzjg.Rows.Count; i++)
            //    {
            //        //if (!IsColumnIncludeData(tbzjg, "INVCODE", dtzjg.Rows[i]["INVCODE"].ToString()))
            //        //{
            //        string sql2;
            //        sql2 = "delete Dic_Inventory where INVCODE='" + dtzjg.Rows[i]["INVCODE"].ToString() + "'";
            //        SqlExecuteSql(SqlconnectionStringZJG, sql2);

            //        sql2 = "insert into Dic_Inventory(guid, INVCODE, INVNAME, INVSHORTNAME, INVSPEC, INVTYPE, INVCLASSCODE, INVCLASSNAME)";
            //        sql2 += " values( newid(),'" + dtzjg.Rows[i]["INVCODE"].ToString() + "','" + dtzjg.Rows[i]["INVNAME"].ToString() + "',";
            //        sql2 += "'" + dtzjg.Rows[i]["INVSHORTNAME"].ToString() + "','" + dtzjg.Rows[i]["INVSPEC"].ToString() + "','" + dtzjg.Rows[i]["INVTYPE"].ToString() + "'";
            //        sql2 += ",'" + dtzjg.Rows[i]["INVCLASSCODE"].ToString() + "','" + dtzjg.Rows[i]["INVCLASSNAME"].ToString() + "')";
            //        SqlExecuteSql(SqlconnectionStringZJG, sql2);

            //        //}


            //    }
            //}
            //zjgtime = DateTime.Now.AddDays(-0.1);
            //sql = "update [Bus_最新时间] set [最新时间]='" + zjgtime + "'";
            //SqlExecuteSql(SqlconnectionStringZJG, sql);
            this.textBox1.Text = "同步时间:" + DateTime.Now.ToString();

        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.timer1.Interval = 60000 * 3;
            this.timer1.Enabled = true;
            this.timer2.Interval = 1000;
            this.timer2.Enabled = true;

            this.pictureBox1.BackColor = Color.FromArgb(192, 255, 192);
        }

        private void button3_Click(object sender, EventArgs e)
        {
            this.timer1.Enabled = false;
            this.timer2.Enabled = false;
            this.pictureBox1.BackColor = Color.Red;
        }

        private void timer2_Tick(object sender, EventArgs e)
        {
            if (this.pictureBox1.BackColor == Color.FromArgb(192, 255, 192))
            {
                this.pictureBox1.BackColor = Color.FromArgb(128, 255, 128);
            }
            else
            {
                this.pictureBox1.BackColor = Color.FromArgb(192, 255, 192);
            }
        }

        private void button岳阳_Click(object sender, EventArgs e)
        {
            取岳阳();
            MessageBox.Show("从NC同步产品到岳阳档案完成!");

        }
        private void 取岳阳()
        {
            //string sql = "truncate table Dic_Inventory ";
            //SqlExecuteSql(SqlconnectionStringYY, sql);

            删重复(SqlconnectionStringYY);

            string sql更新英文 = @" select INVCODE, INVNAME, INVSHORTNAME, INVSPEC, INVTYPE, INVCLASSCODE, INVCLASSNAME ,ts,FORINVNAME  from yy.v_produce where FORINVNAME is not null ";
            DataTable dt更新英文 = OraQuery(sql更新英文);
            if (dt更新英文 != null && dt更新英文.Rows.Count > 0)
            {
                for (int i = 0; i < dt更新英文.Rows.Count; i++)
                {

                    string sql更新 = $@"update Dic_Inventory set FORINVNAME = '{dt更新英文.Rows[i]["FORINVNAME"].ToString()}' where INVCODE = '{dt更新英文.Rows[i]["INVCODE"].ToString()}'";
                        SqlExecuteSql(SqlconnectionStringYY, sql更新);
                }
            }

            string sql;
            sql = @" select INVCODE, INVNAME, INVSHORTNAME, INVSPEC, INVTYPE, INVCLASSCODE, INVCLASSNAME ,ts,FORINVNAME  from yy.v_produce where ts>='" + yytime.ToString("yyyy-MM-dd HH:mm:ss") + "' order by ts asc";
            DataTable dt = OraQuery(sql);
            sql = @"SELECT      INVCODE FROM      Dic_Inventory ";
            DataTable tb = SqlGetDataTable(SqlconnectionStringYY, sql);

            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (!IsColumnIncludeData(tb, "INVCODE", dt.Rows[i]["INVCODE"].ToString()))
                    {
                        string sql2;
                        sql2 = "insert into Dic_Inventory(guid, INVCODE, INVNAME, INVSHORTNAME, INVSPEC, INVTYPE, INVCLASSCODE, INVCLASSNAME,[CreateDate],FORINVNAME)";
                        sql2 += " values( newid(),'" + dt.Rows[i]["INVCODE"].ToString() + "','" + dt.Rows[i]["INVNAME"].ToString() + "',";
                        sql2 += "'" + dt.Rows[i]["INVSHORTNAME"].ToString() + "','" + dt.Rows[i]["INVSPEC"].ToString() + "','" + dt.Rows[i]["INVTYPE"].ToString() + "'";
                        sql2 += ",'" + dt.Rows[i]["INVCLASSCODE"].ToString() + "','" + dt.Rows[i]["INVCLASSNAME"].ToString() + "','" + dt.Rows[i]["ts"].ToString() + "','"+ dt.Rows[i]["FORINVNAME"].ToString()+"')";
                        SqlExecuteSql(SqlconnectionStringYY, sql2);

                    }
                    else
                    {
                        string sql2;
                        sql2 = $"delete from Dic_Inventory where INVCODE = '{dt.Rows[i]["INVCODE"].ToString()}'";
                        SqlExecuteSql(SqlconnectionStringYY, sql2);

                        sql2 = "insert into Dic_Inventory(guid, INVCODE, INVNAME, INVSHORTNAME, INVSPEC, INVTYPE, INVCLASSCODE, INVCLASSNAME,[CreateDate],FORINVNAME)";
                        sql2 += " values( newid(),'" + dt.Rows[i]["INVCODE"].ToString() + "','" + dt.Rows[i]["INVNAME"].ToString() + "',";
                        sql2 += "'" + dt.Rows[i]["INVSHORTNAME"].ToString() + "','" + dt.Rows[i]["INVSPEC"].ToString() + "','" + dt.Rows[i]["INVTYPE"].ToString() + "'";
                        sql2 += ",'" + dt.Rows[i]["INVCLASSCODE"].ToString() + "','" + dt.Rows[i]["INVCLASSNAME"].ToString() + "','" + dt.Rows[i]["ts"].ToString() + "','"+ dt.Rows[i]["FORINVNAME"].ToString() + "')";
                        SqlExecuteSql(SqlconnectionStringYY, sql2);
                    }


                }
                yytime = DateTime.Parse(dt.Rows[dt.Rows.Count - 1]["ts"].ToString());
                sql = "update Bus_最新时间 set  [最新时间]='" + yytime + "'  ";
                SqlExecuteSql(SqlconnectionStringYY, sql);//岳阳

                sql = @"insert into T_VIEW_CIM (T_GOOD_CODE,T_GOOD_NAME,T_GOOD_MODEL,EXP4,T_GOOD_STATUE)
                        select [INVCODE],[INVNAME],[INVSPEC],[INVTYPE],'启用' from  [Dic_Inventory] where  not exists (select 1 from T_VIEW_CIM where T_GOOD_CODE = [INVCODE])
                        and INVCLASSNAME in ( '热轧','锭类')";
                SqlExecuteSql(SqlconnectionString, sql);//岳阳
            }
        }
        /// <summary>
        /// 张家港
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button4_Click(object sender, EventArgs e)
        {
            取张家港();
            MessageBox.Show("从NC同步产品到张家港档案完成!");

        }

        private void 取张家港()
        {

            删重复(SqlconnectionStringZJG);
            string sql;
            //string sql = "truncate table Dic_Inventory ";
            //SqlExecuteSql(SqlconnectionStringZJG, sql);
            sql = @" select INVCODE, INVNAME, INVSHORTNAME, INVSPEC, INVTYPE, INVCLASSCODE, INVCLASSNAME ,ts  from zjg.v_produce where ts>='" + zjgtime.ToString("yyyy-MM-dd HH:mm:ss") + "' order by ts asc";
            DataTable dt = OraQuery(sql);
            sql = @"SELECT      INVCODE FROM      Dic_Inventory ";
            DataTable tb = SqlGetDataTable(SqlconnectionStringZJG, sql);

            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (!IsColumnIncludeData(tb, "INVCODE", dt.Rows[i]["INVCODE"].ToString()))
                    {
                        string sql2;
                        sql2 = "insert into Dic_Inventory(guid, INVCODE, INVNAME, INVSHORTNAME, INVSPEC, INVTYPE, INVCLASSCODE, INVCLASSNAME,[CreateDate])";
                        sql2 += " values( newid(),'" + dt.Rows[i]["INVCODE"].ToString() + "','" + dt.Rows[i]["INVNAME"].ToString() + "',";
                        sql2 += "'" + dt.Rows[i]["INVSHORTNAME"].ToString() + "','" + dt.Rows[i]["INVSPEC"].ToString() + "','" + dt.Rows[i]["INVTYPE"].ToString() + "'";
                        sql2 += ",'" + dt.Rows[i]["INVCLASSCODE"].ToString() + "','" + dt.Rows[i]["INVCLASSNAME"].ToString() + "','" + dt.Rows[i]["ts"].ToString() + "')";
                        SqlExecuteSql(SqlconnectionStringZJG, sql2);

                    }
                    else
                    {
                        string sql2;
                        sql2 = $"delete from Dic_Inventory where INVCODE = '{dt.Rows[i]["INVCODE"].ToString()}'";
                        SqlExecuteSql(SqlconnectionStringZJG, sql2);

                        sql2 = "insert into Dic_Inventory(guid, INVCODE, INVNAME, INVSHORTNAME, INVSPEC, INVTYPE, INVCLASSCODE, INVCLASSNAME,[CreateDate])";
                        sql2 += " values( newid(),'" + dt.Rows[i]["INVCODE"].ToString() + "','" + dt.Rows[i]["INVNAME"].ToString() + "',";
                        sql2 += "'" + dt.Rows[i]["INVSHORTNAME"].ToString() + "','" + dt.Rows[i]["INVSPEC"].ToString() + "','" + dt.Rows[i]["INVTYPE"].ToString() + "'";
                        sql2 += ",'" + dt.Rows[i]["INVCLASSCODE"].ToString() + "','" + dt.Rows[i]["INVCLASSNAME"].ToString() + "','" + dt.Rows[i]["ts"].ToString() + "')";
                        SqlExecuteSql(SqlconnectionStringZJG, sql2);
                    }


                }
                zjgtime = DateTime.Parse(dt.Rows[dt.Rows.Count - 1]["ts"].ToString());
                sql = "update Bus_最新时间 set  [最新时间]='" + zjgtime + "'  ";
                SqlExecuteSql(SqlconnectionStringZJG, sql);//张家港

                sql = @"insert into T_VIEW_CIM (T_GOOD_CODE,T_GOOD_NAME,T_GOOD_MODEL,EXP4,T_GOOD_STATUE)
                        select [INVCODE],[INVNAME],[INVSPEC],[INVTYPE],'启用' from  [Dic_Inventory] where  not exists (select 1 from T_VIEW_CIM where T_GOOD_CODE = [INVCODE])
                        and INVCLASSNAME in ( '热轧','锭类')";
                SqlExecuteSql(SqlconnectionString, sql);//张家港
            }
        }

        private void timer3_Tick(object sender, EventArgs e)
        {
            string sql = "truncate table Dic_Inventory ";
            SqlExecuteSql(SqlconnectionStringZJG, sql);
            sql = @" select * from zjg.v_produce";
            DataTable dtyy = OraQuery(sql);
            sql = @"SELECT      INVCODE FROM      Dic_Inventory ";
            DataTable tbyy = SqlGetDataTable(SqlconnectionStringZJG, sql);

            if (dtyy.Rows.Count > 0)
            {
                for (int i = 0; i < dtyy.Rows.Count; i++)
                {
                    if (!IsColumnIncludeData(tbyy, "INVCODE", dtyy.Rows[i]["INVCODE"].ToString()))
                    {
                        string sql2;
                        sql2 = "insert into Dic_Inventory(guid, INVCODE, INVNAME, INVSHORTNAME, INVSPEC, INVTYPE, INVCLASSCODE, INVCLASSNAME)";
                        sql2 += " values( newid(),'" + dtyy.Rows[i]["INVCODE"].ToString() + "','" + dtyy.Rows[i]["INVNAME"].ToString() + "',";
                        sql2 += "'" + dtyy.Rows[i]["INVSHORTNAME"].ToString() + "','" + dtyy.Rows[i]["INVSPEC"].ToString() + "','" + dtyy.Rows[i]["INVTYPE"].ToString() + "'";
                        sql2 += ",'" + dtyy.Rows[i]["INVCLASSCODE"].ToString() + "','" + dtyy.Rows[i]["INVCLASSNAME"].ToString() + "')";
                        SqlExecuteSql(SqlconnectionStringZJG, sql2);

                    }


                }
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            //var 招财3 = SqlSugarHelp.scope.GetConnection("招采").Queryable<VIEW_CIM>()
            //                .ToList();
            var 招财 = SqlSugarHelp.scope.GetConnection("招采").Queryable<VIEW_CIM>()
                            .Select(it => new T_VIEW_CIM()
                            {
                                T_GOOD_NAME = it.T_GOOD_NAME,
                                T_GOOD_CODE = it.T_GOOD_CODE,
                                T_GOOD_TYPE = it.T_GOOD_TYPE,
                                T_GOOD_TYPE_CODE = it.T_GOOD_TYPE_CODE,
                                T_GOOD_MODEL = it.T_GOOD_MODEL,
                                T_GOOD_UNIT = it.T_GOOD_UNIT,
                                T_GOOD_PRICE_CUR = it.T_GOOD_PRICE_CUR,
                                T_EXC_RATE = it.T_EXC_RATE,
                                T_PRICE_BASE = it.T_PRICE_BASE,
                                T_GOOD_TAX_RATE = it.T_GOOD_TAX_RATE,
                                T_QUALITY = it.T_QUALITY,
                                T_QUALITY_UNIT = it.T_QUALITY_UNIT,
                                T_GOOD_PRICE_UNIT = it.T_GOOD_PRICE_UNIT,
                                T_GOOD_PRICE_UNIT_CODE = it.T_GOOD_PRICE_UNIT_CODE,
                                EXP1 = it.EXP1,
                                T_IS_GOOD = it.T_IS_GOOD,
                                T_GOOD_STATUE = it.T_GOOD_STATUE,
                                T_GOOD_STATUE_CODE = it.T_GOOD_STATUE_CODE,
                                T_QUALITY_STAND = it.T_QUALITY_STAND,
                                T_GOOD_REMARK = it.T_GOOD_REMARK,
                                T_CREATE_DATE = it.T_CREATE_DATE,
                                T_UPDATE_DATE = it.T_UPDATE_DATE,
                                EXP4 = it.EXP4
                            })
                            .ToList();

            foreach (var item in 招财)
            {
                item.guid = Guid.NewGuid();
            }
            SqlSugarHelp.scope.GetConnection("panhuadb").Ado.ExecuteCommand("truncate table T_VIEW_CIM");
            SqlSugarHelp.scope.GetConnection("panhuadb").Insertable(招财).ExecuteCommand();
            SqlSugarHelp.scope.GetConnection("yydb").Ado.ExecuteCommand("truncate table T_VIEW_CIM");
            SqlSugarHelp.scope.GetConnection("yydb").Insertable(招财).ExecuteCommand();
            SqlSugarHelp.scope.GetConnection("zjgdb").Ado.ExecuteCommand("truncate table T_VIEW_CIM");
            SqlSugarHelp.scope.GetConnection("zjgdb").Insertable(招财).ExecuteCommand();

        }

        private void Form1_Load(object sender, EventArgs e)
        {
            string sql;
            sql = "SELECT [最新时间] from Bus_最新时间";
            DataTable cqtb = SqlGetDataTable(SqlconnectionString, sql);
            if (cqtb.Rows.Count > 0)
            {
                cqtime = DateTime.Parse(cqtb.Rows[0]["最新时间"].ToString());
            }
            DataTable yytb = SqlGetDataTable(SqlconnectionStringYY, sql);
            if (yytb.Rows.Count > 0)
            {
                yytime = DateTime.Parse(yytb.Rows[0]["最新时间"].ToString());
            }
            DataTable zjgtb = SqlGetDataTable(SqlconnectionStringZJG, sql);
            if (zjgtb.Rows.Count > 0)
            {
                zjgtime = DateTime.Parse(zjgtb.Rows[0]["最新时间"].ToString());
            }

        }

        private void 删重复(string concstr) {
           string sql = $@"WITH DuplicateGroups AS (
                SELECT INVSHORTNAME, INVSPEC, INVTYPE
                FROM [Dic_Inventory]
                GROUP BY INVSHORTNAME, INVSPEC, INVTYPE
                HAVING COUNT(*) > 1
            )
            DELETE FROM [Dic_Inventory]
            WHERE EXISTS (
                SELECT 1 
                FROM DuplicateGroups d
                WHERE d.INVSHORTNAME = [Dic_Inventory].INVSHORTNAME
                  AND d.INVSPEC = [Dic_Inventory].INVSPEC
                  AND d.INVTYPE = [Dic_Inventory].INVTYPE
            );
                WITH CTE AS (
                SELECT guid, INVCODE, CreateDate,
                ROW_NUMBER() OVER (PARTITION BY INVCODE ORDER BY CreateDate DESC) as rn
                FROM [Dic_Inventory]
                )
                DELETE FROM CTE WHERE rn > 1

                ";
            SqlExecuteSql(concstr, sql);
        }
    }
}
